package com.zhq.dao.imp;

import com.zhq.bean.Express;
import com.zhq.dao.BaseExpressDao;
import com.zhq.exception.DuplicateCodeException;
import com.zhq.util.DruidUtil;

import java.sql.*;
import java.util.*;

/**
 * @Author:钟豪强
 * @Description:
 * @Date Created in  2020-08-16 13:26
 * @Modified By:
 */
public class ExpressDaoMysql implements BaseExpressDao {
    //用于查询数据库中的全部快递数(总数和新增)和代取件数(总数和新增)
    private static String SQL_CONSOLE = "SELECT COUNT(ID) DATA1_SIZE, COUNT(TO_DAYS(INTIME) = TO_DAYS(NOW()) OR NULL) DATA1_DAY ,COUNT(STATUS = 0 OR NULL) DATA2_SIZE, COUNT(TO_DAYS(INTIME) = TO_DAYS(NOW()) AND STATUS = 0 OR NULL) DATA2_DAY FROM EXPRESS";
    //用于查询所有快递信息
    private static String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    //用于分页查询所有快递信息
    private static String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    //用于根据取件码查询快递信息
    private static String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE = ?";
    //用于根据id查询快递信息
    private static String SQL_FIND_BY_ID = "SELECT * FROM EXPRESS WHERE ID = ?";
    //用于根据快递单号查询快递信息
    private static String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER = ?";
    //用于根据录入人手机号码查询快递信息
    private static String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE = ?";
    //用于根据取件人手机号码查询所有快递信息
    private static String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE = ?";
    //用于根据取件人手机号码和快递状态,查询所有符合状态的快递信息
    private static String SQL_FIND_BY_USERPHONE_AND_STATUS = "SELECT * FROM EXPRESS WHERE USERPHONE = ? AND STATUS = ?";
    //用录入快递
    private static String SQL_INSERT = "INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
    //生成新的取件码
    private static String SQL_UPDATE_CODE = " UPDATE EXPRESS SET CODE = ? WHERE ID = ? ";
    //修改取件人的手机号码
    private static String SQL_UPDATE_USERPHONE = " UPDATE EXPRESS SET USERPHONE = ? WHERE ID = ? ";
    //修改快递信息
    private static String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER = ?, USERNAME = ?, COMPANY = ?, STATUS = ? WHERE ID = ?";
    //修改快递状态码(取快递)
    private static String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS = 1,OUTTIME = NOW(),CODE = NULL WHERE CODE = ?";
    //修改录入号码
    private static String SQL_UPDATE_SYSPHONE = " UPDATE EXPRESS SET SYSPHONE = ? WHERE ID = ? ";
    //删除快递
    private static String SQL_DELETE = " DELETE FROM EXPRESS WHERE ID = ? ";


    /**
     * 用于查询数据库中全部快递(总数和新增),代取件快递(总数加新增)
     *
     * @return [{size:总数,day:新增},{size:代取件总数,day:新增代取件}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.    填充参数(可选)
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int data1_size = result.getInt("data1_size");
                int data1_day = result.getInt("data1_day");
                int data2_size = result.getInt("data2_size");
                int data2_day = result.getInt("data2_day");
                //建立两个map只是为了区分总快递数和代取件快递
                Map map1 = new HashMap();
                map1.put("data1_size",data1_size);
                map1.put("data1_day",data1_day);
                Map map2 = new HashMap();
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);
                data.add(map1);
                data.add(map2);
                return data;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 查询所有快递
     *
     * @param limit      表示是否分页,true是分页,false是不分页
     * @param offset     SQL语句的分页起始索引
     * @param pageNumber 页查询的数量
     * @return 返回快递的集合,没有快递信息返回null
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.    填充参数(可选)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }

            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");;
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据快递单号查询快递信息
     *
     * @param number 快递信息
     * @return 返回该单号快递, 单号不存在返回null
     */
    @Override
    public Express findByNumber(String number) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.    填充参数(可选)
            state.setString(1,number);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");;
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据id查询快递信息
     *
     * @param id 快递id
     * @return 返回该id快递, 单号不存在返回null
     */
    @Override
    public Express findById(int id) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_ID);
            //3.    填充参数(可选)
            state.setInt(1,id);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");;
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据快递取件码查询快递信息
     *
     * @param code 取件码
     * @return 返回该取件码快递, 取件码不存在返回null
     */
    @Override
    public Express findByCode(String code) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_CODE);
            //3.    填充参数(可选)
            state.setString(1,code);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String number = result.getString("number");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");;
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据用户手机号,查询他的所有快递信息
     *
     * @param userPhone 手机号
     * @return 快递信息的集合
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.    填充参数(可选)
            state.setString(1,userPhone);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String number = result.getString("number");
                String code = result.getString("code");
                String company = result.getString("company");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     *用于根据取件人手机号码和快递状态,查询所有符合状态的快递信息
     *
     * @param userPhone 手机号
     * @param status    快递状态
     * @return 快递信息的集合
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        ArrayList<Express> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE_AND_STATUS);
            //3.    填充参数(可选)
            state.setString(1,userPhone);
            state.setInt(2,status);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String number = result.getString("number");
                String code = result.getString("code");
                String company = result.getString("company");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                String sysPhone = result.getString("sysphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据录入人手机号码,查询录入的所有记录
     *
     * @param sysPhone 录入人手机号
     * @return 快递信息的集合
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        ResultSet result = null;
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3.    填充参数(可选)
            state.setString(1,sysPhone);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String number = result.getString("number");
                String code = result.getString("code");
                String company = result.getString("company");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");;
                int status = result.getInt("status");
                String userPhone = result.getString("userphone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 快递的录入    (要处理两个异常,快递单号重复,取件码重复)
     *
     * @param express 录入的快递对象
     * @return 录入的结果, true表示成功, false表示失败
     */
    @Override
    public boolean insert(Express express) throws DuplicateCodeException{
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        //2.预编译
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //int id, String number, String username, String userPhone,
            // String company, String code, Timestamp inTime, Timestamp outTime, int status, String sysPhone
            //3.填充参数
            state.setString(1,express.getNumber());
            state.setString(2,express.getUsername());
            state.setString(3,express.getUserPhone());
            state.setString(4,express.getCompany());
            state.setString(5,express.getCode());
            state.setString(6,express.getSysPhone());
            //4.执行SQL语句
            int i = state.executeUpdate();
            //5.获取执行结果
            return i > 0;
            //6.释放资源
        } catch (Exception e1) {
            if (e1.getMessage().endsWith("for key 'code'")){
                //是因为取件码重复,而出现的异常
                DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else{
                e1.printStackTrace();
            }
        } finally {
            DruidUtil.close(conn,state,null);
        }

        return false;
    }

    /**
     * (根据id)快递的修改
     *
     * @param id         要修改的快递id
     * @param newExpress 新的的快递对象(number,company,username,userPhone,status)
     * @return 修改的结果, true代表修改成功, false代表修改失败
     */
    @Override
    public boolean update(int id, Express newExpress) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //3.    填充参数(可选)
            state.setString(1,newExpress.getNumber());
            state.setString(2,newExpress.getUsername());
            state.setString(3,newExpress.getCompany());
            state.setInt(4,newExpress.getStatus());
            state.setInt(5,id);
            //4.    执行SQL语句,获取执行结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据快递id,修改取件码
     *  (要处理异常,取件码重复)
     * @param id      快递id
     * @param newCode  新的取件码
     * @return true代表修改成功
     */
    @Override
    public boolean updateCode(int id, String newCode) throws DuplicateCodeException {
        //1.    获取连接
        Connection conn = DruidUtil.getConnection();
        //2.    预编译
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_CODE);
            //3.    填充参数
            state.setString(1,newCode);
            state.setInt(2,id);
            //4.    执行
            return state.executeUpdate() > 0 ? true:false;
        } catch (Exception e) {
            if (e.getMessage().endsWith("for key 'code'")) {
                //是因为取件码重复,而出现的异常
                DuplicateCodeException e2 = new DuplicateCodeException(e.getMessage());
                throw e2;
            }else{
                e.printStackTrace();
            }
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 更改快递的状态为1,表示取件的完成
     *
     * @param code 要修改的取件码
     * @return 修改的结果, true表示成功, false表示失败
     * (这里还没有写完,后期更改一下)
     */
    @Override
    public boolean updateStatus(String code) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_STATUS);
            //3.    填充参数(可选)
            state.setString(1,code);
            //4.    执行SQL语句,获取执行的结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据快递id,修改sysPhone
     *
     * @param id
     * @param sysPhone 录入人手机号码
     * @return true代表修改成功
     */
    @Override
    public boolean updateSysPhone(int id, String sysPhone) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_SYSPHONE);
            //3.    填充参数(可选)
            state.setString(1,sysPhone);
            state.setInt(2,id);
            //4.    执行SQL语句,获取执行的结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据快递id  修改收件人手机号码
     *
     * @param id        快递id
     * @param userPhone 收件人手机号码
     * @return true代表修改成功
     */
    @Override
    public boolean updateUserPhone(int id, String userPhone) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_USERPHONE);
            //3.    填充参数(可选)
            state.setString(1,userPhone);
            state.setInt(2,id);
            //4.    执行SQL语句,获取执行的结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id,删除单个快递信息
     *
     * @param id 要删除的快递信息
     * @return 删除的结果, true表示成功, false表示失败
     */
    @Override
    public boolean delete(int id) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            //3.    填充参数(可选)
            state.setInt(1,id);
            //4.    执行SQL语句,获取执行的结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
